/**
 * 
 */
package com.htht.smartschool.service.bigexam;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.bigexam.AdminExamResultBean;
import com.htht.smartschool.model.bigexam.BigExamBean;
import com.htht.smartschool.model.bigexam.ParentExamBean;
import com.htht.smartschool.model.bigexam.ParentExamResultBean;
import com.htht.smartschool.model.bigexam.SmallTestBean;
import com.htht.smartschool.model.bigexam.SubjectInfoBean;
import com.htht.smartschool.model.bigexam.TeacherExamResultBean;
import com.htht.smartschool.model.bigexam.TotalListBean;

/**
 * @author wuqiwei
 *
 */
public class ResultService extends BaseService{
	@SuppressWarnings({ "unused", "rawtypes" })
	private Map parentChilderenTotalRank = new HashMap();//获取家长端孩子的总成绩 学校排名
	@SuppressWarnings("rawtypes")
	private Map maprank = new HashMap();//放排名的
	@SuppressWarnings("rawtypes")
	private Map mapscore = new HashMap();//放成绩的
	@SuppressWarnings("rawtypes")
	private Map mapclassrank = new HashMap();//单科的班级排名
	@SuppressWarnings({ "rawtypes", "unused" })
	private Map studentInfoMap = new HashMap();//放学生信息的map
	@SuppressWarnings({ "rawtypes", "unused" })
	private Map classInfoMAp = new HashMap();//存入班级的map
	@SuppressWarnings("rawtypes")
	private Map parentChildrenSchoolRank = new HashMap();//获取家长端孩子的单科成绩 学校排名
	/**导出管理员 查看每科考试学生成绩的信息
	 * @author:wuqiwei
	 * @param @param coursecode
	 * @param @param examcode
	 * @param @param schoolcode
	 * @param @param schooltype
	 * @param @param classcode
	 * @param @return
	 * @date:2016年6月29日上午10:08:17
	*/
	public List<AdminExamResultBean> getEveryExamInfo(String coursecode,
			String examcode, String schoolcode, String schooltype,
			String classcode,String grades) {
		getEveryExamSchoolRank(coursecode,examcode,schoolcode,schooltype,classcode,grades);//获取单科成绩的班级排名
		//家长端查看的时候 只会有一个孩子传入进来
//		big.setStudentcode(big.getStudentcode().replace(",", "^"));
		String sql = " SELECT t.*,@rownum \\:= @rownum+1 FROM (SELECT *,@rownum \\:= 0 FROM t_result WHERE coursecode = '"+coursecode+"' AND examcode = '"+examcode+"' and schoolcode = '"+schoolcode+"' and type = '"+schooltype+"'";
		if(classcode != null && classcode.length() > 0){
			sql += " and classcode = '"+classcode+"'";
		}
			sql += " ORDER BY scorenumber*1 DESC) t";
		List list =  getDao().queryBySQL(sql);
			
		List<AdminExamResultBean> result = new ArrayList<AdminExamResultBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			AdminExamResultBean admin = new AdminExamResultBean();
			admin.setStudentname(String.valueOf(obj[5]));
			admin.setStudentcode(String.valueOf(obj[4]));
			admin.setResult(String.valueOf(obj[10]));
			admin.setClasses(String.valueOf(obj[6]));
			admin.setSubjectname(String.valueOf(obj[3]));
			double schoolrank = Double.valueOf(String.valueOf(obj[15]));
			Long schrank =  Math.round(schoolrank);
			admin.setShoolrank(String.valueOf(schrank));
			admin.setClassrank(String.valueOf(mapclassrank.get(String.valueOf(obj[4]))));
			result.add(admin);
		}
		return result;
	}
	
	/**获取单科成绩的班级排名
	 * @author:wuqiwei
	 * @param @param big
	 * @date:2016年6月24日下午1:21:36
	*/
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void getEveryExamSchoolRank(String coursecode,
			String examcode, String schoolcode, String schooltype,
			String classcode,String grades) {
		String sql = "SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode,rank "
				+ " FROM(SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode,@rownum\\:=@rownum+1,IF(@classcode=classcode,@rank\\:=@rank+1,@rank\\:=1) AS rank, @classcode\\:=classcode "
				+ " FROM (SELECT examcode,coursecode,coursename,studentname,studentcode,classesname,grade,classes,classcode,scorenumber,schoolcode "
				+ " FROM t_result WHERE examcode = '"+examcode+"' AND coursecode = '"+coursecode+"' AND grade = '"+grades+"' and schoolcode ='"+schoolcode+"' and type = '"+schooltype+"' ";
				if(classcode != null && classcode.length() > 0){
					sql += " and classcode = '"+classcode+"'";
				}
				sql += " ORDER BY classes ASC, scorenumber*1 DESC) result_tmp, (SELECT @rownum \\:=0 , @classcode \\:= NULL ,@rank\\:=0) tmp ) result";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double classrank = Double.valueOf(String.valueOf(obj[11]));
			mapclassrank.put(String.valueOf(obj[4]),Math.round(classrank));
		}
	}

	/**导出教师端 成绩列表excel
	 * @author:wuqiwei
	 * @param @param examcode
	 * @param @param studentcode
	 * @param @return
	 * @date:2016年6月29日下午1:16:19
	*/
	public List<TeacherExamResultBean> getTeacherResultList(String examcode,
			String studentcode,String classcode) {
		queryStudentClass(classcode);
		queryStudent(classcode);
		String sql = " select tt.* from (SELECT  t.classcode,t.examcode,t.studentcode,tc.grade,tc.class,tc.schoolcode,t.result,@rownum \\:= @rownum+1,te.remark FROM (SELECT *,@rownum \\:= 0 FROM t_examinationresult te WHERE examcode = '"+examcode+"' ORDER BY result*1 desc) t,(SELECT * FROM t_class) tc,(SELECT * FROM t_exam)te WHERE t.classcode = tc.classcode  AND te.examcode = t.examcode) tt where 1 = 1";
		if(studentcode != null && studentcode.length() > 0){
			sql += " and tt.studentcode = '"+studentcode+"'";
		}
		List<Object[]> 	list = getDao().queryBySQL(sql);
		List<TeacherExamResultBean> result = new ArrayList<TeacherExamResultBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			TeacherExamResultBean teacher =  new TeacherExamResultBean();
			double rank = Double.valueOf(String.valueOf(obj[7]));
//			teahcer.setClassranking(String.valueOf(Math.round(rank)));
			String studentname = String.valueOf(studentInfoMap.get(String.valueOf(obj[2])));
			teacher.setStudentname(studentname);
			teacher.setStudentcode(String.valueOf(obj[2]));
			teacher.setResult(String.valueOf(obj[6]));
			teacher.setClasses((String) classInfoMAp.get(String.valueOf(obj[0])));
			double schoolrank = Double.valueOf(String.valueOf(obj[7]));
			Long schrank =  Math.round(schoolrank);
			teacher.setExamname(String.valueOf(obj[8]));
			teacher.setClassrank(String.valueOf(schrank));
			result.add(teacher);
		}
		return result;
	}
	/**
	 * 查学生姓名
	 * @author:wuqiwei
	 * @param @param classcode
	 * @date:2016年6月29日下午1:28:23
	 */
	public void queryStudent(String classcode){
		String sql = "SELECT studentcode,NAME FROM t_student WHERE classcode = '"+classcode+"'";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			studentInfoMap.put(String.valueOf(obj[0]), String.valueOf(obj[1]));//根据studentcode唯一 存入学生的姓名
		}
	}
	/**
	 * 获取班级
	 * @author:wuqiwei
	 * @param @param classcode
	 * @date:2016年6月29日下午1:34:36
	 */
	@SuppressWarnings("unchecked")
	public void queryStudentClass(String classcode){
		String sql = " SELECT * FROM t_class WHERE classcode = '"+classcode+"'";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			classInfoMAp.put(String.valueOf(obj[1]), String.valueOf(obj[2])+'年'+String.valueOf(obj[3])+'班');
		}
	}

	/**导出家长端一科成绩成绩单详情
	 * @author:wuqiwei
	 * @param @param coursecode
	 * @param @param examcode
	 * @param @param schoolcode
	 * @param @param schooltype
	 * @param @param classcode
	 * @param @param grades
	 * @param @param studentcode
	 * @param @return
	 * @date:2016年6月29日下午1:54:43
	*/
	public List<ParentExamResultBean> getParentChildrenOneEXamResult(
			String coursecode, String examcode, String schoolcode,
			String schooltype, String classcode, String grades,
			String studentcode) {
		queryStudentClass(classcode);
		queryParentChildrenOneSubjectSchoolRank(coursecode,examcode,schoolcode);//查询家长端一个孩子一科考试的学校排名
		getEveryExamSchoolRank(coursecode,examcode,schoolcode,schooltype,classcode,grades);//获得孩子单科的班级排名
		String sql = " SELECT t1.* FROM (SELECT * FROM t_result WHERE studentcode = '"+studentcode+"' AND examcode = '"+examcode+"' AND schoolcode = '"+schoolcode+"' AND coursecode = '"+coursecode+"' ) t1";
		List list = getDao().queryBySQL(sql);
		List<ParentExamResultBean> result = new ArrayList<ParentExamResultBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			ParentExamResultBean bean = new ParentExamResultBean();
			bean.setStudentname(String.valueOf(obj[5]));
			bean.setStudentcode(String.valueOf(obj[4]));
			bean.setResult(String.valueOf(obj[10]));
			bean.setClasses((String) classInfoMAp.get(String.valueOf(obj[9])));
			Long schoolrank = (Long) parentChildrenSchoolRank.get(String.valueOf(obj[4]));
			bean.setSubjename(String.valueOf(obj[3]));
			bean.setShoolrank(String.valueOf(schoolrank));//校排名
			bean.setClassrank(String.valueOf(mapclassrank.get(String.valueOf(obj[4]))));//班级排名
			result.add(bean);
		}
		return result;
	}
	
	/**
	 * 查询家长端一个孩子一科考试的学校排名
	 * @author:wuqiwei
	 * @param 
	 * @date:2016年6月25日上午11:33:40
	 */
	@SuppressWarnings("unchecked")
	public void queryParentChildrenOneSubjectSchoolRank(String coursecode,String examcode,String schoolcode ){
		String sql = " SELECT t.*,@rownum \\:= @rownum+1 FROM (SELECT *,@rownum \\:= 0 FROM t_result WHERE coursecode = '"+coursecode+"' AND examcode = '"+examcode+"' AND schoolcode = '"+schoolcode+"'   ORDER BY scorenumber*1 DESC) t";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double schoolrank = Double.valueOf(String.valueOf(obj[15]));
			parentChildrenSchoolRank.put(String.valueOf(obj[4]), Math.round(schoolrank));
		}

	}

	/**考试总榜信息
	 * @author:wuqiwei
	 * @param @param examcode
	 * @param @param schoolcode
	 * @param @param grades
	 * @param @param classcode
	 * @param @return
	 * @date:2016年6月29日下午4:30:09
	*/
	public List<TotalListBean> getExamTotalList(String examcode1, String schoolcode,
			String grades, String classcode) {
		Map map = new HashMap();
		getRankingInfo(examcode1,schoolcode);//获取班级排名
		String sql = " SELECT distinct t1.studentname,t1.examcode,t1.studentcode,t1.classesname,t1.coursename,t1.scorenumber,t1.classcode,t1.grade,t1.schoolcode,t3.schoolrank,t3.scorecount,tb.examname FROM "
				+ " (SELECT studentname, examcode, studentcode, classesname, coursename, scorenumber,classcode,grade,schoolcode FROM t_result WHERE examcode = '"+examcode1+"' ORDER BY studentname) t1, "
				+ " (SELECT t2.*, @rownum\\:=@rownum+1 schoolrank FROM (SELECT SUM(scorenumber) scorecount, examcode, studentcode, @rownum \\:= 0 FROM t_result WHERE examcode = '"+examcode1+"' GROUP BY examcode,studentcode ORDER BY scorecount DESC)t2) t3,(SELECT * FROM t_big_exam) tb "
				+ " where t1.examcode = t3.examcode AND tb.examcode = t1.examcode and t1.studentcode = t3.studentcode and t1.schoolcode = '"+schoolcode+"'";
		if(grades != null && grades.length() > 0){
			sql += " and t1.grade = '"+grades+"'";
		}
		if(classcode != null && classcode.length() > 0){
			sql += " and t1.classcode = '"+classcode+"'";
		}
			sql += " ORDER  BY schoolrank,t1.studentname,t1.coursename";
		List<Object[]>  list = getDao().queryBySQL(sql);
		List<TotalListBean> result = new ArrayList<TotalListBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		int j = 0;
		for(int i = 0;i<list.size();i++){
			Object[] obj = list.get(i);
			TotalListBean exam = new TotalListBean();
			SubjectInfoBean sujkect = new SubjectInfoBean();
			String examcode = String.valueOf(obj[1]);
			String studentcode = String.valueOf(obj[2]);
			if(j == 0){
				exam.setStudentname(String.valueOf(obj[0]));
				exam.setStudentcode(String.valueOf(obj[2]));
				exam.setClassesname(String.valueOf(obj[3]));
				exam.setExamcode(String.valueOf(obj[1]));
				sujkect.setSubject(String.valueOf(obj[4]));
				sujkect.setScore(String.valueOf(obj[5]));
//				subject.add(sujkect);
				exam.setSubjectinfo(subject);
				exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
				exam.setClassranking(String.valueOf(maprank.get(studentcode)));
				double schoolrank = Double.valueOf(String.valueOf(obj[9]));
				Long a = Math.round(schoolrank);
				exam.setSchoolrank(a);
				exam.setExamname(String.valueOf(obj[11]));
				result.add(exam);
				j++;
			}else{
				if(examcode.equalsIgnoreCase(result.get(j-1).getExamcode()) && studentcode.equalsIgnoreCase(result.get(j-1).getStudentcode())){//看返回的数据格式 跟第一次做比较 相同的数据就不添加 只添加课程和成绩
					sujkect.setSubject(String.valueOf(obj[4]));
					sujkect.setScore(String.valueOf(obj[5]));
//					subject.add(sujkect);
					exam.setSubjectinfo(subject);
				}else{
					subject = new ArrayList<SubjectInfoBean>();
					exam.setStudentname(String.valueOf(obj[0]));
					exam.setStudentcode(String.valueOf(obj[2]));
					exam.setExamcode(String.valueOf(obj[1]));
					exam.setClassesname(String.valueOf(obj[3]));
					sujkect.setSubject(String.valueOf(obj[4]));
					sujkect.setScore(String.valueOf(obj[5]));
//					subject.add(sujkect);
					exam.setSubjectinfo(subject);
					exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
					exam.setClassranking(String.valueOf(maprank.get(studentcode)));
					double schoolrank = Double.valueOf(String.valueOf(obj[9]));
					Long a = Math.round(schoolrank);
					exam.setSchoolrank(a);
					exam.setExamname(String.valueOf(obj[11]));
					result.add(exam);
					j++;
				}
			}
			subject.add(sujkect);
		}
		return result;
	}
	/**获取班级排名
	 * @author:wuqiwei
	 * @param @return
	 * @date:2016年6月22日下午2:37:11
	*/
	@SuppressWarnings("unchecked")
	private void getRankingInfo(String examcode,String schoolcode) {
		String sql  = " SELECT classes,studentcode,examcode,score,rank "
				+ " FROM ( SELECT classes,studentcode,examcode,score,@rownum\\:=@rownum+1 , IF(@classes=classes,@rank\\:=@rank+1,@rank\\:=1) AS rank, @classes\\:=classes "
				+ " FROM ( SELECT classes,studentcode,examcode,SUM(scorenumber) score FROM t_result WHERE examcode = '"+examcode+"' AND schoolcode = '"+schoolcode+"' GROUP BY studentcode,examcode ORDER BY classes ASC,score DESC ) result_tmp ,"
				+ " (SELECT @rownum \\:=0 , @classes \\:= NULL ,@rank\\:=0) tmp ) result";
		List list = getDao().queryBySQL(sql);
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double rank = Double.valueOf(String.valueOf(obj[4]));
			double scorenum = Double.valueOf(String.valueOf(obj[3]));
			maprank.put(String.valueOf(obj[1]), Math.round(rank));
			mapscore.put(String.valueOf(obj[1]),  Math.round(scorenum));
		}
	}

	/**导出家长端成绩榜
	 * @author:wuqiwei
	 * @param @param examcode
	 * @param @param studentcode
	 * @param @param schoolcode
	 * @param @param classcode
	 * @param @return
	 * @date:2016年7月1日下午5:15:18
	*/
	public List<ParentExamBean> getParentChildrenOneExamRank(String examcode,
			String studentcode, String schoolcode, String classcode,String grades) {
		getRankingInfo(examcode,schoolcode);//获取成绩总分
		getParnentChildrenOneExamSchoolRank(examcode,schoolcode,grades);//获取家长端孩子的成绩 学校排名
		String sql = " SELECT t1.*,tb.examname FROM (SELECT * FROM t_result  WHERE examcode = '"+examcode+"' AND studentcode = '"+studentcode+"' AND schoolcode = '"+schoolcode+"' AND classcode = '"+classcode+"') t1,(SELECT * FROM t_big_exam) tb WHERE 1 = 1 AND  t1.examcode = tb.examcode";
		List list = getDao().queryBySQL(sql);
		List<ParentExamBean> result = new ArrayList<ParentExamBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		int j = 0;
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			ParentExamBean exam = new ParentExamBean();
			SubjectInfoBean sujkect = new SubjectInfoBean();
			String examcode1 = String.valueOf(obj[1]);
			String studentcode1 = String.valueOf(obj[4]);
			if(j == 0){
				exam.setStudentname(String.valueOf(obj[5]));
				exam.setStudentcode(String.valueOf(obj[4]));
				exam.setClassesname(String.valueOf(obj[6]));
				exam.setExamcode(String.valueOf(obj[1]));
				sujkect.setSubject(String.valueOf(obj[3]));
				sujkect.setScore(String.valueOf(obj[10]));
				exam.setSubjectinfo(subject);
				exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
				Long schoolrank = (Long) parentChilderenTotalRank.get(studentcode);
				exam.setSchoolrank(schoolrank);
				exam.setExamname(String.valueOf(obj[14]));
				result.add(exam);
				j++;
			}else{
				if(examcode1.equalsIgnoreCase(result.get(j-1).getExamcode()) && studentcode.equalsIgnoreCase(result.get(j-1).getStudentcode())){//看返回的数据格式 跟第一次做比较 相同的数据就不添加 只添加课程和成绩
					sujkect.setSubject(String.valueOf(obj[3]));
					sujkect.setScore(String.valueOf(obj[10]));
					exam.setSubjectinfo(subject);
				}else{
					subject = new ArrayList<SubjectInfoBean>();
					exam.setStudentname(String.valueOf(obj[5]));
					exam.setStudentcode(String.valueOf(obj[4]));
					exam.setExamcode(String.valueOf(obj[1]));
					exam.setClassesname(String.valueOf(obj[6]));
					sujkect.setSubject(String.valueOf(obj[3]));
					sujkect.setScore(String.valueOf(obj[10]));
					exam.setSubjectinfo(subject);
					exam.setScorenumber(String.valueOf(mapscore.get(studentcode)) );
					Long schoolrank = (Long) parentChilderenTotalRank.get(studentcode);
					exam.setSchoolrank(schoolrank);
					exam.setExamname(String.valueOf(obj[14]));
					result.add(exam);
					j++;
				}
			}
			subject.add(sujkect);
		
		}
		return result;
	}

	/**获取家长端孩子的成绩 学校排名
	 * @author:wuqiwei
	 * @param @param big
	 * @date:2016年6月25日下午3:48:55
	*/
	@SuppressWarnings("unchecked")
	private void getParnentChildrenOneExamSchoolRank(String examcode,String schoolcode,String grades) {
		String sql = " SELECT t1.*,t3.schoolrank,t3.scorecount FROM  (SELECT studentname, examcode, studentcode, classesname, coursename, scorenumber,classcode,grade,schoolcode FROM t_result WHERE examcode = '"+examcode+"' ORDER BY studentname) t1,  (SELECT t2.*, @rownum\\:=@rownum+1 schoolrank FROM (SELECT SUM(scorenumber) scorecount, examcode, studentcode, @rownum \\:= 0 FROM t_result WHERE examcode = '"+examcode+"' GROUP BY examcode,studentcode ORDER BY scorecount DESC)t2) t3  WHERE t1.examcode = t3.examcode AND t1.studentcode = t3.studentcode AND t1.schoolcode = '"+schoolcode+"' AND t1.grade = '"+grades+"'";
		List list = getDao().queryBySQL(sql);
		List<BigExamBean> result = new ArrayList<BigExamBean>();
		List<SubjectInfoBean> subject = new ArrayList<SubjectInfoBean>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = (Object[]) list.get(i);
			double schoolrank = Double.valueOf(String.valueOf(obj[9]));
			parentChilderenTotalRank.put(String.valueOf(obj[2]), Math.round(schoolrank));
		}
	}
}
